﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;


namespace gui
{
    class DataBase
    {
        SqlConnection _connection;

        private string _compName;
        private string _dbName;
        private string _tableName;

        Config config = new Config();

        /// <summary>
        /// Инициализируем переменные
        /// </summary>
        /// <param name="name">Имя Базы</param>
        /// <param name="table">Имя таблицы</param>
        public DataBase()
        {
            config.GetConfig();
        }
        public DataBase(string dbname, string tableName, string compName)
        {
            this._compName = compName;
            this._dbName = dbname;
            this._tableName = tableName;
        }

        /// <summary>
        /// Подключается к базе данных по имени комьютера и имени БД
        /// </summary>
        /// <param name="compName">Имя компьютера</param>
        /// <param name="dataBase">Имя базы данных</param>
        public void OpenConnection()
        {
            _connection = new SqlConnection(@"Data Source=" + config.ComputerName + ";Initial Catalog=" + config.DataBaseName + ";Integrated Security=True");
            try
            {

                _connection.Open();
            }
            catch (SqlException ex)
            {
                string errorMsg = String.Format("Can not connect to database... \n Error details: \n {0}", ex.Message);
                MessageBox.Show(errorMsg, "Ошибка");
            }
        }

        /// <summary>
        /// Подключается к базе данных по имени комьютера и имени БД
        /// </summary>
        /// <param name="compName">Имя компьютера</param>
        /// <param name="dataBase">Имя базы данных</param>
        public void OpenConnection(string computerName, string dbName)
        {
            SqlConnection connection;
            connection = new SqlConnection(@"Data Source=" + computerName + ";Initial Catalog=" + dbName + ";Integrated Security=True");
            try
            {

                connection.Open();
            }
            catch (SqlException ex)
            {
                if (ex.Number == 4060)
                {
                    creatDataBase();
                }
                else
                {
                    string errorMsg = String.Format("Can not connect to database... \n Error details: \n {0}", ex.Message);
                    MessageBox.Show(errorMsg, "Ошибка");
                }
            }
        }

        /// <summary>
        /// Закрывает соединение с БД
        /// </summary>
        private void _CloseConnection()
        {
            _connection.Close();
        }

        /// <summary>
        /// Записывает в базу данные
        /// </summary>
        /// <param name="message">Сообщение</param>
        /// <param name="login">Логин</param>
        /// <param name="date">Дата</param>
        public void CreatData(string msg, DateTime date)
        {

            string str = "Insert into " + config.DataBaseTable + " (message,date) Values (@val1,@val3)";

            try
            {
                SqlCommand cmd = new SqlCommand(str, _connection);
                cmd.Parameters.AddWithValue("@val1", msg);
                cmd.Parameters.AddWithValue("@val3", date);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string txt = "Insert Error:";
                txt += ex.Message;
                throw new Exception(txt);

            }
            finally
            {
                Console.WriteLine("Сообщение успешно добавлено в базу");
                _CloseConnection();
            }
        }

        public List<SqlResult> getData()
        {
            var result = new List<SqlResult>();
            string queryString = "SELECT message, date from dbo."+config.DataBaseTable;

            SqlCommand command = new SqlCommand(queryString, _connection);
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        SqlResult res = new SqlResult();
                        res.ColumnCount = reader.FieldCount;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            res.ColumnNames.Add(reader.GetName(i));
                            res.Types.Add(reader.GetFieldType(i));
                            res.Values.Add(reader.GetValue(i));
                        }
                        result.Add(res);
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Ошибка");
            }
            return result;
        }

        /// <summary>
        /// Создаем базу и таблицу
        /// </summary>
        public void creatDataBase()
        {
            string CreateDatabase = "CREATE DATABASE " + this._dbName;
            string CreateTable = "CREATE TABLE " + this._tableName + " (id INTEGER PRIMARY KEY IDENTITY," +
                "message TEXT NOT NULL ," +
                "date DATETIME NOT NULL)";

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = "workstation id=;initial catalog=; integrated security=true";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = CreateDatabase;
            cmd.Connection = connection;

            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch
            {
                MessageBox.Show(" База данных существует ", "Ошибка");
            }
            finally
            {
                connection.Close();
            }

            connection.ConnectionString = "workstation id=;initial catalog=" + this._dbName + "; integrated security=true";
            try
            {
                connection.Open();
                //    Выполняем запрос добавление таблицы
                cmd.CommandText = CreateTable;
                cmd.ExecuteNonQuery();
            }
            catch
            {
                MessageBox.Show(" Таблица с именем " + this._tableName + " существует в базе данных ", "Ошибка");
            }
            finally
            {
                MessageBox.Show("База данных успешно создана", "Поздравляем");
                connection.Close();
            }
        }

        /// <summary>
        /// Проверяем существует ли база
        /// </summary>
        public uint dbExist()
        {
            uint state = 1;
            string queryString = "select TABLE_CATALOG, TABLE_NAME from information_schema.tables";

            SqlCommand command = new SqlCommand(queryString, _connection);
            try
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                if (reader[0].ToString() == this._dbName || reader[1].ToString() == this._tableName)
                {
                    //Console.WriteLine("{0} : {1}", reader[0],reader[1]);
                    reader.Close();
                    state = 0;
                }                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка");
            }
            return state;
        }
    }
}
